﻿using Dapper;
using BgWorks.Common;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Data;
using System.Linq;
using System.Text;

namespace BgWorks.Repository
{
    /// <summary>
    /// Oracle查询类 jscontroller使用
    ///  2022年2月22日
    /// </summary>
    public class OracleRepository
    {
        /// <summary>
        /// 通过sql语句查询dynamic结果
        /// 增加判断连接英文字符集数据库Connection  2023-1-6
        /// </summary>
        /// <param name="connKey">连接字符串key</param>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public dynamic QueryByOracle(string connKey, string sql)
        {
            try
            {
                IDbConnection con;
                //if (DapperFactory.ConnType == Entity.Enum.ConnectionType.OracleEn)
                //{
                //    con = DapperFactory.CreateOracleEnConnection(connKey);
                //}
                //else
                //{
                    con = DapperFactory.CreateOracleConnection(connKey);
                //}
                using (con)
                {
                    var result = con.Query(sql);
                    if (result.Any())
                    {
                        return result;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.GetLog().Error(ex);
                return null;
            }
        }
        /// <summary>
        /// 通过sql语句查询datatable结果
        /// 增加判断连接英文字符集数据库Connection
        /// </summary>
        /// <param name="connKey">连接字符串key</param>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public dynamic OracleGetDataTable(string connKey, string sql)
        {
            try
            {
                if (string.IsNullOrEmpty(sql))
                {
                    return null;
                }
                IDbConnection con;
                //if (DapperFactory.ConnType == Entity.Enum.ConnectionType.OracleEn)
                //{
                //    con = DapperFactory.CreateOracleEnConnection(connKey);
                //}
                //else
                //{
                    con = DapperFactory.CreateOracleConnection(connKey);
                //}
                using (con)
                {
                    using (var cmd = con.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        using (var reader = cmd.ExecuteReader())
                        {
                            var dt = new DataTable();
                            dt.Load(reader);
                            return dt;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.GetLog().Error(ex);
                return null;
            }
        }


        /// <summary>
        /// 作为一个事务执行的sql（多个更新，插入）
        /// </summary>
        /// <param name="connKey"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string ExecuteByOracle(string connKey, string sql)
        {
            try
            {
                using (var conn = DapperFactory.CreateOracleConnection(connKey))
                {       
                    try
                    {
                        conn.Execute(sql, null);

                    }
                    catch (Exception ex)
                    {
                        LogHelper.GetLog().Error("接口工具（多条sql）:" + ex);
                        return "false";
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.GetLog().Error("接口工具（多条sql）:" + ex);
                return "false";
            }
            return "true";
        }
        /// <summary>
        /// 向数据库里插入Blob格式的字段
        /// </summary>
        public int ExecuteBlobSql(string connKey, string strSQL, string strBlob)
        {
            return 0;
            //using (var connection = DapperFactory.CreateDefaultConnection(connKey))
            //{
            //    var cmd = connection.CreateCommand();
            //    cmd.CommandText = strSQL;
            //    var myParameter = DapperFactory.GetProcParameter(new OracleParameter(":Blob", OracleDbType.Blob) { Value = Convert.FromBase64String(strBlob) });
            //    cmd.Parameters.Add(myParameter);
            //    try
            //    {
            //        int rows = cmd.ExecuteNonQuery();
            //        return rows;
            //    }
            //    catch (Exception ex)
            //    {
            //        LogHelper.GetLog().Error(ex);
            //        return 0;
            //    }
            //    finally
            //    {
            //        cmd.Dispose();
            //    }
            //}
        }
    }
}
